--KIEM TRA DUOC PHEP MO SO HAY KHONG
IF OBJECT_ID (N'dbo.KIEMTRA_SL_STK') IS NOT NULL
    DROP FUNCTION dbo.KIEMTRA_SL_STK
GO

CREATE FUNCTION dbo.KIEMTRA_SL_STK(@MA_KH CHAR(10))
RETURNS BIT
AS
BEGIN
DECLARE @SL INT,@SL_STK_TOIDA INT,@FLAG BIT
SELECT @SL=COUNT(*) FROM SOTIETKIEM WHERE MA_KH=@MA_KH
SELECT @SL_STK_TOIDA=SOLUONG_STK_TOIDA FROM THAMSO
IF(@SL<@SL_STK_TOIDA)
SET @FLAG=1;
ELSE
SET @FLAG=0;
RETURN @FLAG
END
--KIEM TRA SO TIEN KHI MO SO
IF OBJECT_ID (N'dbo.KIEMTRA_SOTIENGUI') IS NOT NULL
    DROP PROC dbo.KIEMTRA_SOTIENGUI
GO

CREATE PROC dbo.KIEMTRA_SOTIENGUI @SOTIENGUI FLOAT,@KYHAN VARCHAR(30),@TEN_DONVITIEN VARCHAR(10),@FLAG BIT OUTPUT
AS
BEGIN
DECLARE @SOTIENGUI_TOITHIEU_COKYHAN FLOAT,@SOTIENGUI_TOITHIEU_KHONGKYHAN FLOAT,@MAKH VARCHAR(30),@MA_LOAITIETKIEM VARCHAR(30),@TYLE_VND_USD FLOAT
SELECT @SOTIENGUI_TOITHIEU_COKYHAN=SOTIENGUI_TOITHIEU_COKYHAN,@SOTIENGUI_TOITHIEU_KHONGKYHAN=SOTIENGUI_TOITHIEU_KHONGKYHAN,@TYLE_VND_USD=VND_USD FROM THAMSO
IF @KYHAN>0
BEGIN
		IF LEN(@TEN_DONVITIEN)>0
		BEGIN
				IF @TEN_DONVITIEN='VND'
				BEGIN
						SET @SOTIENGUI=@SOTIENGUI*@TYLE_VND_USD
						PRINT @SOTIENGUI
				END

		END
		IF @SOTIENGUI>=@SOTIENGUI_TOITHIEU_COKYHAN
		BEGIN
				SET @FLAG=1
		END
		ELSE
		BEGIN
				SET @FLAG=0
		END
END
IF @KYHAN=0
BEGIN
		IF LEN(@TEN_DONVITIEN)>0
		BEGIN
				IF @TEN_DONVITIEN='VND'
				BEGIN
						SET @SOTIENGUI=@SOTIENGUI*@TYLE_VND_USD
						PRINT @SOTIENGUI
				END

		END
		IF @SOTIENGUI>=@SOTIENGUI_TOITHIEU_KHONGKYHAN
		BEGIN
				SET @FLAG=1 
		END
		ELSE
		BEGIN
				SET @FLAG=0
		END
END
END
DECLARE @SOTIEN FLOAT,@X BIT
SET @SOTIEN=100

EXEC  dbo.KIEMTRA_SOTIENGUI 2000,'9','VND',@X OUTPUT
PRINT @X
SELECT *
FROM SOTIETKIEM
--KIEM  TRA THONG TIN KHACH HANG
IF OBJECT_ID (N'dbo.PROC_KIEMTRATHONGTIN_KHACHHANG') IS NOT NULL
    DROP PROC dbo.PROC_KIEMTRATHONGTIN_KHACHHANG
CREATE PROC dbo.PROC_KIEMTRATHONGTIN_KHACHHANG @Ten varchar(30),@CMND varchar(30),@Flag bit output
as 
declare cs cursor for select MA_KH from KHACH_HANG
open cs
declare @vtt varchar(100),@HoTen varchar(30),@SoCMND varchar(30)
fetch next from cs into @vtt
while @@FETCH_STATUS=0
begin
	SELECT @HoTen=HOTEN,@SoCMND=SOCMND FROM KHACH_HANG
    WHERE MA_KH = @vtt
	if @Ten=@HoTen and @CMND=@SoCMND
	begin
			set @Flag=1
	end
	else
	begin
			set @Flag=0
	end
    fetch next from cs into @vtt
end
close cs
deallocate cs 
go
